/*
 * ====================================================================
 * $Id: HermesDB.java 12188 2006-04-27 04:50:46Z patsch $
 * ====================================================================
 *
 * (C) 2001-2005 KJEW Investments (ACN 116279380) 
 *               as trustee for the Dockhorn Family Trust
 *               trading as SKEX (ABN 96 381 807 305)
 *                     
 * [All rights reserved]. This product and related documentation are protected
 * by copyright restricting its use, copying, distribution, and decompilation.
 * No part of this product or related documentation may be reproduced in any
 * form by any means without prior written authorization of SKEX.
 * Unless otherwise arranged, third parties may not have access to this product
 * or related documents.
 *
 */

package com.lagerplan.basisdienste.util.persistence;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.log4j.Logger;

import db.MyDB;
import util.config.XProperties;
import util.logging.LogLoader;

/**
 * This class wraps direct JDBC connections to a specific database using a pool. 
 * Use it for JDBC style connections (no hibernate stuff).
 * 
 * If you need a new connection pool for a new DB, do the following:
 * (1) copy one of the implementations, i.e. SMSGWDB.java to YourDB.java
 * (2) rename HermesDB to YourDB in the file
 * (3) change the location of the property file (PROP_RESOURCE) below
 * (4) change the name of the pool name (POOL_NAME) below
 * (5) Change the url, username and password - look for @TODO below 
 * (6) See IlpSQL.java - you need to copy & adjust this as well !
 *
 * @author Patrick Dockhorn
 * @version $Revision: 12188 $, $Date: 2006-04-27 14:50:46 +1000 (Thu, 27 Apr 2006) $
 */


public class ExcelDB extends MyDB
{
	//====================================================================
	//      CONSTANTS 
	//
	// @TODO : You have to change the two values below if you want to add
	//         a new Database that is to be used through this interface.
	//
	//====================================================================
	
	private static final String PROP_RESOURCE =
			  LogLoader.CLASSES_HOME + "config/db/db.properties";

	// prefix to be used to allow Hermes to re-use properties in
	// the shared db.properties file, but use its own for specific stuff
	// such as connection properties
	
	private static final String EXCEL_DBPROPS_PREFIX = "excel.";
	
	private static final String POOL_NAME = "excelPool";  

	//====================================================================
	//                     MEMBERS
	//====================================================================
	private static Logger log = LogLoader.getLogger(ExcelDB.class);
	
	protected static XProperties properties = null;
	private static ObjectPool connectionPool = null;
	private static PoolableConnectionFactory poolableConnectionFactory = null;

	/**
	 * A reference to the single instance of this class is stored in this variable.
	 * This is how the class maintains the Singleton behavior.
	 */
	private static ExcelDB db_instance;

	//====================================================================
	//                     CONSTRUCTORS
	//====================================================================
	
	/**
	 * class constructor
	 */
	private ExcelDB()
	{
		try
		{
			initialize();
		}
		catch (Exception e)
		{
			log.error("Error during initialization of Database: " + e.getMessage() );
		}
	}
	//====================================================================
	//                     METHODS
	//====================================================================

	/**
	 * This method returns a reference to the single running instance
	 * and if this is the first time the class is called it creates the single instance.
	 * @return HermesDB_instance Single instance of the HermesDB object.
	 */
	public static synchronized ExcelDB getInstance()
	{
		if (db_instance == null)
		{
			// Must be the first call to the object
			db_instance = new ExcelDB();
			if (log.isInfoEnabled()) log.info("ExcelDB instance successfully created!");

		}
		return db_instance;
	}


	/**
	 * Initializes the Connection Pool.
	 * This method is called from the Initializer-Servlet
	 * @throws Exception exception
	 */
	private static void initialize() throws Exception
	{

		if (log.isInfoEnabled())
		{
			log.info("[ExcelDB] starting initialization");
		}

		if (log.isDebugEnabled())
			log.debug("starting intitialization of properties");

		properties = new XProperties();
		if (!properties.initFromFile(PROP_RESOURCE))
		{
			log.error("Could not initialise properties from ["+PROP_RESOURCE+"]");
		}

		if (log.isDebugEnabled())
			log.debug("properties initialized");
		
		connectionPool = getObjectPool(properties);


		// Load the JDBC driver
		String driverName = properties.getProperty(EXCEL_DBPROPS_PREFIX+"db.driver", "com.mysql.jdbc.Driver");
		Class.forName(driverName).newInstance();

		// --------------------------------------------------------
		// @TODO
		// you may need to change the following three lines for a new DB !
		
		//Get the configuration from the properties file
		String url = properties.getProperty(EXCEL_DBPROPS_PREFIX+"db.url");
		if (null==url)
		{
			log.error("No db.url defined in db.properties file - cannot establish JDBC connection to DB.");
		}

		String username = properties.getProperty(EXCEL_DBPROPS_PREFIX+"db.user", "");
		String password = properties.getProperty(EXCEL_DBPROPS_PREFIX+"db.password", "");

		if (log.isDebugEnabled())
			log.debug("Trying to get connection to ["
					  + url + "] with username [" + username + "]");


		// Next, we'll create a ConnectionFactory that the
		// pool will use to create Connections.
		// We'll use the DriverManagerConnectionFactory,
		// using the connect string passed in the command line
		// arguments.
		ConnectionFactory connectionFactory = null;

		// Next, we'll create a ConnectionFactory that the
		// pool will use to create Connections.
		// We'll use the DriverManagerConnectionFactory,
		// using the connect string passed in the command line
		// arguments.
		//
		connectionFactory = new DriverManagerConnectionFactory(url, username, password);

		//
		// Now we'll create the PoolableConnectionFactory, which wraps
		// the "real" Connections created by the ConnectionFactory with
		// the classes that implement the pooling functionality.
		// "SELECT 1" is the validation query executed before a connection
		// is used, to validate, that the connection is still alive and not
		// cut off by the sql-server.
		//
		poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, "SELECT 1", false, true);

		//
		// Finally, we create the PoolingDriver itself...
		//
		PoolingDriver driver = new PoolingDriver();

		//
		// ...and register our pool with it.
		//
		try
		{
			driver.registerPool(POOL_NAME, connectionPool);
		}
		catch (Exception e)
		{
			log.error("couldn't register pool: " + e.getMessage());
		}
        if (log.isInfoEnabled())
        {
		log.info("Successfully registered pool ["+POOL_NAME+"] with connection pool ["+connectionPool.toString()+"]");
        }
		//
		// Now we can just use the connect string "jdbc:apache:commons:dbcp:mojo_db"
		// to access our pool of Connections.
		//
	}


	/**
	 * returns the database-properties
	 *
	 * @return XProperties database properties
	 * @throws Exception whatever exception
	 */
	public static XProperties getProperties() throws Exception
	{
		getInstance();
		return properties;
	}

	/**
	 * Returns the Pool Name in use
	 * @return <code>PoolName</code>
	 */
	
	public String getPoolName()
	{
		return POOL_NAME;
	}
	
	/**
	 * close all opened connection to the db
	 */
	public static void destroy()
	{
		if (log.isInfoEnabled())
			log.info("trying to close all open physical connections to the database...");

		if (db_instance != null)
		{
			try
			{
				if (log.isInfoEnabled())
					log.info("Closing connection pool");
				connectionPool.close();
				poolableConnectionFactory.destroyObject(connectionPool);
            if (log.isInfoEnabled())
					log.info("Connection Pool closed");
			}
			catch (Exception e)
			{

				log.error("unable to close database conenctions.\n" +
						  "This should NOT happen, because then your database\nwill run out of connections soon! " + e.getMessage());
			}
			finally
			{
				db_instance = null;
				if (log.isInfoEnabled())
					log.info("ExcelDB instance closed");
			}
		}

	}
	
	/**
	 * @throws Throwable when finalizing
	 */
	protected void finalize() throws Throwable
	{
		super.finalize();
	}
	
	/**
	 * 
	 * @return String PROP_RESOURCE
	 */
	public static String getPropResource()
	{
		return PROP_RESOURCE;
	}
}

